// routes/douyin_project_analysis.js
import express from "express";
import db from "../db/mysql.js";
import {getDateRange, roundTo2} from "../dingtalk/utils.js";
import {
    getZongKanBanDatas,
    getZhengTiChengJiaoLineDatasRes,
    getShopDatas
} from "../dingtalk/DouyinProjectAnalysisIndexDatas.js";

const router = express.Router();


/*抖音项目分析首页数据*/

router.post('/index_datas', async (req, res) => {
    try {

        // ✅ 1️⃣ 改成从 body 获取参数
        const {date_or_month, star_parm, end_parm} = req.body;
        console.log(`date_or_month:${date_or_month}   star_parm:${star_parm}  end_parm:${end_parm}`)
        const allDates = await getDateRange(star_parm, end_parm);
        const sql_params = [];
        sql_params.push(star_parm);
        sql_params.push(end_parm);
        sql_params.push(star_parm);
        sql_params.push(end_parm);
        sql_params.push(star_parm);
        sql_params.push(end_parm);
        sql_params.push(star_parm);
        sql_params.push(end_parm);
        /*抖音项目分析总看板 sql  数据 */
        const zong_kan_ban_datas_res = await getZongKanBanDatas(date_or_month, star_parm, end_parm)


        /*整体成交_折线图  sql + datas*/
        let zheng_ti_cheng_jiao_line_datas_res = await getZhengTiChengJiaoLineDatasRes(date_or_month, star_parm, end_parm, allDates)


        /*整体成交_每个店铺指标数据 sql + datas */
        let shop_datas = await getShopDatas(date_or_month, star_parm, end_parm, allDates)


        res.json({
            "zong_kan_ban_datas": zong_kan_ban_datas_res,
            "zheng_ti_cheng_jiao_line_datas": zheng_ti_cheng_jiao_line_datas_res,
            "shop_datas": shop_datas
        });


    } catch (err) {
        console.error(err);
        res.status(500).json({error: "数据库查询失败"});
    }
});


router.post('/shop_self_coop_detail', async (req, res) => {

    let res_data = []

    try {

        const {date_or_month, star_parm, end_parm, shop_name, is_self, type} = req.body;
        const allDates = await getDateRange(star_parm, end_parm);

        let sql = ``
        let shang_pin_ka_qian_chuang_sql = ``
        if (date_or_month === "date") {

            sql = `
            SELECT account_or_channel, sum(user_payment_amount) as user_payment_amount,
        sum(refund_amount_payment_time) as refund_amount_payment_time FROM dd_carrier_composition_datas 
        WHERE 1=1 AND date BETWEEN ? AND ? AND shop_name=? AND is_self=? AND type=?  GROUP BY  account_or_channel;
`

            /*自营直播*/
            if (type === "直播" && is_self==="self"){
                sql = `
            SELECT 
    a.account_or_channel,

    -- 主表：用户支付金额 & 退款金额
    ROUND(a.user_payment_amount,2) as user_payment_amount,
    ROUND(a.refund_amount_payment_time,2) as refund_amount_payment_time,

    -- 千川消耗
    ROUND(IFNULL(qc.all_cost, 0),2) AS all_cost,

    -- 挂铁 & 补单
    ROUND(IFNULL(s.order_subsidy, 0),2) AS order_subsidy,
    ROUND(IFNULL(s.hanging_fee, 0),2) AS hanging_fee,

    -- 人工成本
    ROUND(IFNULL((h.cheng_ben+(a.user_payment_amount-a.refund_amount_payment_time)* 0.011), 0),2) AS cheng_ben,

    -- 分佣
    ROUND(IFNULL(r.commission_rate, 0),2) AS commission_rate,

    -- ① 签收率
    IF(a.user_payment_amount > 0,
       ROUND((a.user_payment_amount - a.refund_amount_payment_time) * 100 / a.user_payment_amount, 2),
       0
    ) AS qian_shou_lv,

    -- ② ROI
    IF((IFNULL(qc.all_cost,0) + IFNULL(s.hanging_fee,0)) > 0,
       ROUND((a.user_payment_amount - IFNULL(s.order_subsidy,0)) 
             / (IFNULL(qc.all_cost,0) + IFNULL(s.hanging_fee,0)), 2),
       0
    ) AS roi,

    -- ③ 业务利润
    ROUND(
        ((a.user_payment_amount - a.refund_amount_payment_time) * IFNULL(r.commission_rate,0)
        - IFNULL(qc.all_cost,0)
        - IFNULL(s.hanging_fee,0)
        - IFNULL((h.cheng_ben+(a.user_payment_amount-a.refund_amount_payment_time)* 0.011),0)),
        2
    ) AS ye_wu_li_run,

    -- ④ 利润率
    IF(a.user_payment_amount > 0,
       ROUND(
           ((a.user_payment_amount - a.refund_amount_payment_time) * IFNULL(r.commission_rate,0)
            - IFNULL(qc.all_cost,0)
            - IFNULL(s.hanging_fee,0)
            - IFNULL((h.cheng_ben+(a.user_payment_amount-a.refund_amount_payment_time)* 0.011),0)) 
           * 100 / a.user_payment_amount,
           2
       ),
       0
    ) AS li_run_lv


FROM (
    -- 主表：用户支付金额 + 用户退款金额
    SELECT 
        account_or_channel,
        SUM(user_payment_amount) AS user_payment_amount,
        SUM(refund_amount_payment_time) AS refund_amount_payment_time
    FROM dd_carrier_composition_datas
    WHERE date BETWEEN ? AND ?
      AND shop_name = ?
      AND is_self = ?
      AND type = ?
    GROUP BY account_or_channel
) a

-- 分佣比例
LEFT JOIN dd_douyin_shop_commission_rate r
    ON a.account_or_channel = r.douyin_nickname

-- 千川消耗
LEFT JOIN (
    SELECT 
        dou_yin_hao_name AS account_or_channel,
        ROUND(SUM(all_cost), 2) AS all_cost
    FROM (
        SELECT dou_yin_hao_name, all_cost FROM dd_qian_chuan_pu_tong_zheng_ti
        WHERE date BETWEEN ? AND ? 
        UNION ALL
        SELECT dou_yin_hao_name, all_cost FROM dd_qian_chuan_quanyu_douyin_hao
        WHERE date BETWEEN ? AND ? 
        UNION ALL
        SELECT dou_yin_hao_name, all_cost FROM dd_qian_chuan_quanyu_touhou
        WHERE date BETWEEN ? AND ? 
    ) t
    GROUP BY dou_yin_hao_name
) qc ON a.account_or_channel = qc.account_or_channel

-- 挂铁 + 补单
LEFT JOIN (
    SELECT 
        douyin_nickname AS account_or_channel,
        ROUND(SUM(order_subsidy), 2) AS order_subsidy,
        ROUND(SUM(hanging_fee), 2) AS hanging_fee
    FROM dd_self_live_summary
    WHERE date BETWEEN ? AND ? 
    GROUP BY douyin_nickname
) s ON a.account_or_channel = s.account_or_channel

-- 人工成本
LEFT JOIN (
        SELECT 
            live_room as account_or_channel,
            SUM(cost) AS cheng_ben
        FROM (
        
            /* ① 直播人工成本（按店铺） */
            SELECT 
                live_room,
                SUM(day_cost) AS cost
            FROM dd_day_live_cheng_ben
            WHERE date BETWEEN ? AND ?
            GROUP BY live_room
        
            UNION ALL
        
            /* ② 兼职人工成本（按店铺） */
            SELECT 
                douyin_nickname as live_room,
                SUM(salary_day) AS cost
            FROM dd_employee_salary_jian_zhi_base
            WHERE date BETWEEN ? AND ?
            GROUP BY douyin_nickname
        
            UNION ALL
        
            /* ③ 公共工资分摊成本（按店铺） */
            SELECT 
                live_room,
                SUM(
                    ROUND(
                        (DATEDIFF(
                            LEAST(t.last_day, ?),
                            GREATEST(t.first_day, ?)
                        ) + 1) * t.avg_day_live_room_cost
                    , 2)
                ) AS cost
            FROM (
                SELECT 
                    month_str,
                    dou_yin_hao as live_room,
                    ROUND(SUM(avg_day_live_room_cost), 2) AS avg_day_live_room_cost,
                    STR_TO_DATE(CONCAT(month_str, '-01'), '%Y-%m-%d') AS first_day,
                    LAST_DAY(STR_TO_DATE(CONCAT(month_str, '-01'), '%Y-%m-%d')) AS last_day
                FROM dd_employee_salary_pubilc_base
                GROUP BY month_str, dou_yin_hao
            ) t
            WHERE t.last_day >= ?
              AND t.first_day <= ?
            GROUP BY live_room
        
        ) AS merged
        GROUP BY live_room
) h ON a.account_or_channel = h.account_or_channel

ORDER BY a.account_or_channel;

            `
            }
            /*自营商品卡*/
            if (type === "商品卡" && is_self==="self"){
                shang_pin_ka_qian_chuang_sql=`
                SELECT ROUND(SUM(all_cost), 2) AS all_cost FROM dd_qian_chuan_quanyu_plan
                WHERE \`date\` BETWEEN ? AND ? 
            `
            }




        } else {
            sql = `SELECT account_or_channel, sum(user_payment_amount) as user_payment_amount,
sum(refund_amount_payment_time) as refund_amount_payment_time FROM dd_carrier_composition_datas 
WHERE 1=1 AND month_str BETWEEN ? AND ? AND shop_name=? AND is_self=? AND type=?  GROUP BY  account_or_channel;`

            /*自营直播 所有数据sql*/
            if (type === "直播" && is_self==="self"){
                sql = `
            SELECT 
    a.account_or_channel,

    -- 主表：用户支付金额 & 退款金额
    ROUND(a.user_payment_amount,2) as user_payment_amount,
    ROUND(a.refund_amount_payment_time,2) as refund_amount_payment_time,

    -- 千川消耗
    ROUND(IFNULL(qc.all_cost, 0),2) AS all_cost,

    -- 挂铁 & 补单
    ROUND(IFNULL(s.order_subsidy, 0),2) AS order_subsidy,
    ROUND(IFNULL(s.hanging_fee, 0),2) AS hanging_fee,

    -- 人工成本
    ROUND(IFNULL((h.cheng_ben+(a.user_payment_amount-a.refund_amount_payment_time)* 0.011), 0),2) AS cheng_ben,

    -- 分佣
    ROUND(IFNULL(r.commission_rate, 0),2) AS commission_rate,

    -- ① 签收率
    IF(a.user_payment_amount > 0,
       ROUND((a.user_payment_amount - a.refund_amount_payment_time) * 100 / a.user_payment_amount, 2),
       0
    ) AS qian_shou_lv,

    -- ② ROI
    IF((IFNULL(qc.all_cost,0) + IFNULL(s.hanging_fee,0)) > 0,
       ROUND((a.user_payment_amount - IFNULL(s.order_subsidy,0)) 
             / (IFNULL(qc.all_cost,0) + IFNULL(s.hanging_fee,0)), 2),
       0
    ) AS roi,

    -- ③ 业务利润
    ROUND(
        ((a.user_payment_amount - a.refund_amount_payment_time) * IFNULL(r.commission_rate,0)
        - IFNULL(qc.all_cost,0)
        - IFNULL(s.hanging_fee,0)
        - IFNULL((h.cheng_ben+(a.user_payment_amount-a.refund_amount_payment_time)* 0.011),0)),
        2
    ) AS ye_wu_li_run,

    -- ④ 利润率
    IF(a.user_payment_amount > 0,
       ROUND(
           ((a.user_payment_amount - a.refund_amount_payment_time) * IFNULL(r.commission_rate,0)
            - IFNULL(qc.all_cost,0)
            - IFNULL(s.hanging_fee,0)
            - IFNULL((h.cheng_ben+(a.user_payment_amount-a.refund_amount_payment_time)* 0.011),0)) 
           * 100 / a.user_payment_amount,
           2
       ),
       0
    ) AS li_run_lv


FROM (
    -- 主表：用户支付金额 + 用户退款金额
    SELECT 
        account_or_channel,
        SUM(user_payment_amount) AS user_payment_amount,
        SUM(refund_amount_payment_time) AS refund_amount_payment_time
    FROM dd_carrier_composition_datas
    WHERE month_str BETWEEN ? AND ?
      AND shop_name = ?
      AND is_self = ?
      AND type = ?
    GROUP BY account_or_channel
) a

-- 分佣比例
LEFT JOIN dd_douyin_shop_commission_rate r
    ON a.account_or_channel = r.douyin_nickname

-- 千川消耗
LEFT JOIN (
    SELECT 
        dou_yin_hao_name AS account_or_channel,
        ROUND(SUM(all_cost), 2) AS all_cost
    FROM (
        SELECT dou_yin_hao_name, all_cost FROM dd_qian_chuan_pu_tong_zheng_ti
        WHERE \`year_month\` BETWEEN ? AND ? 
        UNION ALL
        SELECT dou_yin_hao_name, all_cost FROM dd_qian_chuan_quanyu_douyin_hao
        WHERE \`year_month\` BETWEEN ? AND ? 
        UNION ALL
        SELECT dou_yin_hao_name, all_cost FROM dd_qian_chuan_quanyu_touhou
        WHERE \`year_month\` BETWEEN ? AND ? 
    ) t
    GROUP BY dou_yin_hao_name
) qc ON a.account_or_channel = qc.account_or_channel

-- 挂铁 + 补单
LEFT JOIN (
    SELECT 
        douyin_nickname AS account_or_channel,
        ROUND(SUM(order_subsidy), 2) AS order_subsidy,
        ROUND(SUM(hanging_fee), 2) AS hanging_fee
    FROM dd_self_live_summary
    WHERE \`month\` BETWEEN ? AND ? 
    GROUP BY douyin_nickname
) s ON a.account_or_channel = s.account_or_channel

-- 人工成本
LEFT JOIN (
    
SELECT 
    live_room as account_or_channel,
    SUM(cost) AS cheng_ben
FROM (

    /* ① 直播人工成本 */
    SELECT 
        live_room,
        SUM(day_cost) AS cost
    FROM dd_day_live_cheng_ben
    WHERE LEFT(date, 7) BETWEEN ? AND ?
    GROUP BY live_room

    UNION ALL

    /* ② 兼职人工成本 */
    SELECT 
        douyin_nickname as live_room,
        SUM(salary_day) AS cost
    FROM dd_employee_salary_jian_zhi_base
    WHERE LEFT(date, 7) BETWEEN ? AND ?
    GROUP BY douyin_nickname

    UNION ALL

    /* ③ 公共工资分摊成本（整月） */
    SELECT 
        live_room,
        SUM(
            ROUND(avg_day_cost * month_days, 2)
        ) AS cost
    FROM (
        SELECT 
            month_str,
            dou_yin_hao as live_room,
            ROUND(SUM(avg_day_live_room_cost), 2) AS avg_day_cost,
            DAY(LAST_DAY(STR_TO_DATE(CONCAT(month_str, '-01'), '%Y-%m-%d'))) AS month_days
        FROM dd_employee_salary_pubilc_base
        WHERE month_str BETWEEN ? AND ?
        GROUP BY month_str, dou_yin_hao
    ) AS t
    GROUP BY live_room

) AS merged
GROUP BY live_room

) h ON a.account_or_channel = h.account_or_channel

ORDER BY a.account_or_channel;

            `
            }


            /*自营商品卡 千川消耗sql*/
            if (type === "商品卡" && is_self==="self"){
                shang_pin_ka_qian_chuang_sql=`
                        SELECT ROUND(SUM(all_cost), 2) AS all_cost FROM dd_qian_chuan_quanyu_plan
        WHERE \`year_month\` BETWEEN ? AND ?  `
            }

        }



        let user_pay_total = 0;  //支付总金额
        let refund_amount_payment_total = 0; //退款总金额
        let refund_rate_total = 0; //总的退款比例
        let qc_cost = 0; //总千川消耗
        let account_data_json = {}
        let qc_cost_shang_oin_ka = 0;  //商品卡千川总消耗



        const sql_params = [];
        //-- 主表：用户支付金额 + 用户退款金额
        sql_params.push(star_parm);
        sql_params.push(end_parm);
        sql_params.push(shop_name);
        if (is_self === "self") {
            sql_params.push("自营");
        } else {
            sql_params.push("带货");
        }
        sql_params.push(type);
        if (type === "直播" && is_self === "self"){
            //千川消耗
            sql_params.push(star_parm);
            sql_params.push(end_parm);
            sql_params.push(star_parm);
            sql_params.push(end_parm);
            sql_params.push(star_parm);
            sql_params.push(end_parm);
            sql_params.push(star_parm);
            sql_params.push(end_parm);
            //人工成本
            sql_params.push(star_parm);
            sql_params.push(end_parm);
            sql_params.push(star_parm);
            sql_params.push(end_parm);
            if (date_or_month === "date"){
                sql_params.push(end_parm);
                sql_params.push(star_parm);
            }
            sql_params.push(star_parm);
            sql_params.push(end_parm);
        }

        const shang_pin_ka_qian_chuang_sql_params = [];
        if (type === "商品卡" && is_self === "self"){
            shang_pin_ka_qian_chuang_sql_params.push(star_parm);
            shang_pin_ka_qian_chuang_sql_params.push(end_parm);
            const shang_pin_ka_qian_chuang_datas = await db.query(shang_pin_ka_qian_chuang_sql, shang_pin_ka_qian_chuang_sql_params);
            for (const data of shang_pin_ka_qian_chuang_datas) {
                try {
                    qc_cost_shang_oin_ka += parseFloat(data["all_cost"]);
                }catch (e) {
                }

            }
        }



        const sql_datas = await db.query(sql, sql_params);


        for (const data of sql_datas) {

            let account_or_channel = data["account_or_channel"]
            if (!(account_or_channel in account_data_json)) {
                account_data_json[account_or_channel] = {};
                account_data_json[account_or_channel]["pay_total"] = 0; //抖音号对应 总金额
                account_data_json[account_or_channel]["pay_total_rate"] = 0 //抖音号对应 总金额比例
                account_data_json[account_or_channel]["refund_total"] = 0 //抖音号对应 退款金额
                account_data_json[account_or_channel]["refund_total_date"] = 0 //抖音号对应 退款比例

                if (type === "直播" && is_self === "self"){
                    account_data_json[account_or_channel]["all_cost"] = 0 //抖音号对应 千川消耗
                    account_data_json[account_or_channel]["cheng_ben"] = 0 //抖音号对应 人工成本
                    account_data_json[account_or_channel]["qian_shou_lv"] = 0 //抖音号对应 签收率
                    account_data_json[account_or_channel]["roi"] = 0 //抖音号对应 ROI
                    account_data_json[account_or_channel]["ye_wu_li_run"] = 0 //抖音号对应 业务利润
                    account_data_json[account_or_channel]["li_run_lv"] = 0 //抖音号对应 利润率
                }

            }

            let user_payment_amount = data["user_payment_amount"]
            user_pay_total += parseFloat(user_payment_amount);
            account_data_json[account_or_channel]["pay_total"] += parseFloat(user_payment_amount);
            let refund_amount_payment_time = data["refund_amount_payment_time"]
            refund_amount_payment_total += parseFloat(refund_amount_payment_time);
            account_data_json[account_or_channel]["refund_total"] += parseFloat(refund_amount_payment_time)


            if (type === "直播" && is_self === "self"){
                qc_cost += parseFloat(data["all_cost"])
                account_data_json[account_or_channel]["all_cost"] += parseFloat(data["all_cost"]) //抖音号对应 千川消耗
                account_data_json[account_or_channel]["cheng_ben"] += parseFloat(data["cheng_ben"]) //抖音号对应 人工成本
                account_data_json[account_or_channel]["qian_shou_lv"] += parseFloat(data["qian_shou_lv"]) //抖音号对应 签收率
                account_data_json[account_or_channel]["roi"] += parseFloat(data["roi"]) //抖音号对应 ROI
                account_data_json[account_or_channel]["ye_wu_li_run"] += parseFloat(data["ye_wu_li_run"]) //抖音号对应 业务利润
                account_data_json[account_or_channel]["li_run_lv"] += parseFloat(data["li_run_lv"]) //抖音号对应 利润率
            }


        }

        qc_cost = await roundTo2(qc_cost)
        for (const account in account_data_json) {
            //用户支付金额 占比
            let pay_total = await roundTo2(account_data_json[account]["pay_total"]);
            let pay_total_rate = 0
            if (user_pay_total > 0) {
                pay_total_rate = (await roundTo2(pay_total * 100 / user_pay_total))
            }

            //用户退款金额 占比
            let refund_total = await roundTo2(account_data_json[account]["refund_total"]);
            let refund_total_date = 0;
            if (refund_amount_payment_total > 0) {
                refund_total_date = (await roundTo2(refund_total * 100 / refund_amount_payment_total))
            }



            let res_data_item={}
            if (type!=="商品卡"){

                const all_cost = account_data_json[account]["all_cost"]//抖音号对应 千川消耗
                const cheng_ben = account_data_json[account]["cheng_ben"]//抖音号对应 人工成本
                const qian_shou_lv = account_data_json[account]["qian_shou_lv"]//抖音号对应 签收率

                const roi = account_data_json[account]["roi"]//抖音号对应 ROI
                const ye_wu_li_run = account_data_json[account]["ye_wu_li_run"]//抖音号对应 业务利润
                const li_run_lv = account_data_json[account]["li_run_lv"]//抖音号对应 利润率

                 res_data_item = {
                    "抖音昵称": account, "支付金额": pay_total, "支付比例": pay_total_rate + "%",
                    "退款金额": refund_total, "退款比例": refund_total_date + "%",
                    "千川消耗": all_cost, "人工成本": cheng_ben, "签收率": qian_shou_lv + "%",
                    "ROI": roi, "业务利润": ye_wu_li_run, "利润率": li_run_lv + "%", "千川总消耗": qc_cost
                }
            }else {
                res_data_item = {
                    "渠道": account, "支付金额": pay_total, "支付比例": pay_total_rate + "%",
                    "退款金额": refund_total, "退款比例": refund_total_date + "%", "千川总消耗": qc_cost_shang_oin_ka
                }
            }

            res_data.push(res_data_item);
        }

        res.json({
            "data": res_data
        });


    } catch (err) {
        console.error(err);
        res.status(500).json({error: "查询失败"});
    }
});


router.post('/shop_self_coop_detail_v1', async (req, res) => {

    let res_data = []

    try {

        const {date_or_month, star_parm, end_parm, shop_name, is_self, type} = req.body;
        const allDates = await getDateRange(star_parm, end_parm);

        let sql = ``
        if (date_or_month === "date") {
            sql = `SELECT account_or_channel, DATE_FORMAT(date, '%Y-%m-%d') AS date,sum(user_payment_amount) as user_payment_amount,
sum(refund_amount_payment_time) as refund_amount_payment_time FROM dd_carrier_composition_datas 
WHERE 1=1 AND date BETWEEN ? AND ? AND shop_name=? AND is_self=? AND type=?  GROUP BY  account_or_channel;`
        } else {
            sql = `SELECT account_or_channel, DATE_FORMAT(date, '%Y-%m-%d') AS date,sum(user_payment_amount) as user_payment_amount,
sum(refund_amount_payment_time) as refund_amount_payment_time FROM dd_carrier_composition_datas 
WHERE 1=1 AND month_str BETWEEN ? AND ? AND shop_name=? AND is_self=? AND type=?  GROUP BY  account_or_channel;`
        }

        const sql_params = [];
        sql_params.push(star_parm);
        sql_params.push(end_parm);
        sql_params.push(shop_name);
        if (is_self === "self") {
            sql_params.push("自营");
        } else {
            sql_params.push("带货");
        }
        sql_params.push(type);
        const sql_datas = await db.query(sql, sql_params);

        let user_pay_total = 0;  //支付总金额
        let refund_amount_payment_total = 0; //退款总金额
        let refund_rate_total = 0; //总的退款比例

        let account_data_json = {}

        for (const data of sql_datas) {

            let account_or_channel = data["account_or_channel"]
            if (!(account_or_channel in account_data_json)) {
                account_data_json[account_or_channel] = {};
                account_data_json[account_or_channel]["pay_total"] = 0; //抖音号对应 总金额
                account_data_json[account_or_channel]["pay_total_rate"] = 0 //抖音号对应 总金额比例
                account_data_json[account_or_channel]["refund_total"] = 0 //抖音号对应 退款金额
                account_data_json[account_or_channel]["refund_total_date"] = 0 //抖音号对应 退款比例
            }

            let user_payment_amount = data["user_payment_amount"]
            user_pay_total += parseFloat(user_payment_amount);
            account_data_json[account_or_channel]["pay_total"] += parseFloat(user_payment_amount);
            let refund_amount_payment_time = data["refund_amount_payment_time"]
            refund_amount_payment_total += parseFloat(refund_amount_payment_time);
            account_data_json[account_or_channel]["refund_total"] += parseFloat(refund_amount_payment_time)
            let date_str = data["date"]
        }

        for (const account in account_data_json) {
            //用户支付金额 占比
            let pay_total = await roundTo2(account_data_json[account]["pay_total"]);
            let pay_total_rate = 0
            if (user_pay_total > 0) {
                pay_total_rate = (await roundTo2(pay_total * 100 / user_pay_total))
            }

            //用户退款金额 占比
            let refund_total = await roundTo2(account_data_json[account]["refund_total"]);
            let refund_total_date = 0;
            if (refund_amount_payment_total > 0) {
                refund_total_date = (await roundTo2(refund_total * 100 / refund_amount_payment_total))
            }


            let res_data_item = {
                "抖音昵称": account, "支付金额": pay_total, "支付比例": pay_total_rate + "%",
                "退款金额": refund_total, "退款比例": refund_total_date + "%"
            }
            res_data.push(res_data_item);
        }

        res.json({
            "data": res_data
        });


    } catch (err) {
        console.error(err);
        res.status(500).json({error: "查询失败"});
    }
});


router.post('/table_and_tand_line', async (req, res) => {

    let res_data = []

    try {

        const {date_or_month, star_parm, end_parm, shop_name, is_self, type, account_or_channel} = req.body;


        console.log(date_or_month, star_parm, end_parm, shop_name, is_self, type, account_or_channel)

        const allDates = await getDateRange(star_parm, end_parm);

        let sql = ``
        if (date_or_month === "date") {
            sql = `SELECT account_or_channel, DATE_FORMAT(date, '%Y-%m-%d') AS date,user_payment_amount,refund_amount_payment_time FROM dd_carrier_composition_datas 
WHERE 1=1 AND date BETWEEN ? AND ? AND shop_name=? AND is_self=? AND type=? and account_or_channel=? order by date desc ;`
        } else {
            sql = `SELECT account_or_channel, DATE_FORMAT(date, '%Y-%m-%d') AS date,user_payment_amount,refund_amount_payment_time FROM dd_carrier_composition_datas 
WHERE 1=1 AND month_str BETWEEN ? AND ? AND shop_name=? AND is_self=? AND type=? and account_or_channel=? order by date desc ;`
        }

        const sql_params = [];
        sql_params.push(star_parm);
        sql_params.push(end_parm);
        sql_params.push(shop_name);
        if (is_self === "self") {
            sql_params.push("自营");
        } else {
            sql_params.push("带货");
        }
        sql_params.push(type);
        sql_params.push(account_or_channel);
        const sql_datas = await db.query(sql, sql_params);

        // { 日期: "2025-10-01", 支付金额: 8646.54, 支付比例: "14.11%", 退款金额: 4704.0, 退款比例: "51.54%" },

        let user_pay_total = 0;  //支付总金额
        let refund_amount_payment_total = 0; //退款总金额
        let refund_rate_total = 0; //总的退款比例


        let table_detail_json = {}

        for (const data of sql_datas) {
            let user_payment_amount = data["user_payment_amount"]
            user_pay_total += parseFloat(user_payment_amount);
            let refund_amount_payment_time = data["refund_amount_payment_time"]
            refund_amount_payment_total += parseFloat(refund_amount_payment_time);
            let date_str = data["date"]
            table_detail_json[date_str] = data
        }

        const tableData = []
        const trendLines = {}
        trendLines["days"] = []
        trendLines["pay"] = []
        trendLines["refund"] = []
        for (const day of allDates) {
            trendLines["days"].push(day);
            let user_payment_amount = 0;
            let refund_amount_payment = 0;
            //    { 日期: "2025-10-01", 支付金额: 8646.54, 支付比例: "14.11%", 退款金额: 4704.0, 退款比例: "51.54%" },
            if (day in table_detail_json) {

                const itemData = table_detail_json[day]
                user_payment_amount = itemData["user_payment_amount"]
                let user_payment_amount_rate = 0;
                if (user_pay_total > 0) {
                    user_payment_amount_rate = await roundTo2(user_payment_amount / user_pay_total)
                    console.log("user_payment_amount:", user_payment_amount, "  ", "user_pay_total", user_pay_total, "  user_payment_amount_rate:", user_payment_amount_rate)
                }
                refund_amount_payment = itemData["refund_amount_payment_time"]
                let refund_amount_payment_rate = 0;
                if (refund_amount_payment_total > 0) {
                    refund_amount_payment_rate = await roundTo2(refund_amount_payment / refund_amount_payment_total)
                    console.log("refund_amount_payment:", refund_amount_payment, "  ",
                        "refund_amount_payment_total", refund_amount_payment_total, "  refund_amount_payment_rate:", refund_amount_payment_rate)
                }

                let tableDataItem = {
                    "日期": itemData["date"],
                    "支付金额": user_payment_amount, "支付比例": user_payment_amount_rate + "%",
                    "退款金额": refund_amount_payment, "退款比例": refund_amount_payment_rate + "%"
                }
                tableData.push(tableDataItem);
            }

            trendLines["pay"].push(user_payment_amount);
            trendLines["refund"].push(refund_amount_payment);
        }


        res.json({
            "tableData": tableData,
            "trendLines": trendLines
        });


    } catch (err) {
        console.error(err);
        res.status(500).json({error: "查询失败"});
    }
});


export default router;
